﻿CREATE TRIGGER tr_Page_Update_PageNumber
ON dbo.t_Page
FOR UPDATE
AS

IF (@@ROWCOUNT = 0) OR
	 (NOT UPDATE(PageNumber)) OR
	 (TRIGGER_NESTLEVEL(Object_ID('tr_Page_Insert')) > 0) OR
	 (TRIGGER_NESTLEVEL(Object_ID('tr_Page_Delete')) > 0) OR
	 (TRIGGER_NESTLEVEL(Object_ID('tr_Page_Update_PageNumber')) > 1)
	RETURN --IF @PageNumber <= 0 -- move @PageNumber from the end --	SELECT @PageNumber = ISNULL(MAX(PageNumber),0) + @PageNumber	FROM t_Page WHERE ImageID = @ImageID

-- if being moved down
IF EXISTS(SELECT * FROM inserted i INNER JOIN deleted d ON i.ID = d.ID WHERE i.PageNumber > d.PageNumber)
	UPDATE t_Page SET PageNumber = i.PageNumber+1
	FROM t_Page INNER JOIN inserted i ON t_Page.ID = i.ID

-- make space for insert
UPDATE t_Page SET PageNumber = t_Page.PageNumber + 1
FROM t_Page INNER JOIN inserted i
ON t_Page.ImageID = i.ImageID AND
	 t_Page.PageNumber >= i.PageNumber AND
	 t_Page.ID <> i.ID

-- re-paginate
DECLARE @PNs TABLE(Pos int identity,PageID int)
INSERT INTO @PNs(PageID)
SELECT t_Page.ID FROM t_Page INNER JOIN inserted i ON t_Page.ImageID = i.ImageID
ORDER BY t_Page.PageNumber

UPDATE t_Page SET PageNumber = PNs.Pos
FROM t_Page INNER JOIN @PNs PNs ON t_Page.ID = PNs.PageID
